/*
 * Copyright (c) 2009 - Jim Lehmer
 * 
 * Permission is hereby granted, free of charge, to any person
 * obtaining a copy of this software and associated documentation
 * files (the "Software"), to deal in the Software without
 * restriction, including without limitation the rights to use,
 * copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following
 * conditions:
 * 
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 * 
 * You may contact the author at jim [dot] lehmer [at] gmail [dot] com.
 */ 

USE IISLogs -- Change to match the database in which you have LogParser parsing the SharePoint IIS logs.
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- TODO: Parameterize this so that the Web part that calls it can pass in
-- what it wants. That could include what file extensions it is interested
-- in, the number of days to go back, sSiteName, etc. Then those could be
-- specified via browser-accessible properties to the Web part.
--
-- Unfortunately the one thing you CAN'T parameterize is the "TOP n" clause.
-- Bummer. Note that because of security trimming that can occur in the
-- SharePoint searches that happen off of this result set you probably want
-- to set the "TOP n" to somewhere around double (YMMV) the results you want
-- to display to the user, unless your site is wide open on a read basis to
-- all or most of your users. Then you could set them to be equal.
ALTER PROC dbo.GetTopHits
AS
SELECT TOP 15 -- Set this to about double the docs the Web part will want to display to the user.
    csUriStem,
	COUNT(*) AS Hits
FROM
	IISLogs.dbo.LogTable
WHERE
	date > DATEADD(day, -15, GETDATE())
	AND RIGHT(csUriStem, 4) IN ('.aspx', '.doc', 'docx', '.xls', 'xlsx', '.ppt', 'pptx', '.pdf')
	AND LEFT(csUriStem, 10) <> '/personal/' -- Exclude My Sites. Can exclude other sites just as easily.
	AND scStatus >= 200 -- Only want to bring back things that 
	AND scStatus < 400  --   have been successfully served.
	AND sSitename = 'W3SVC422211221' -- Use this to narrow down search to specific Web.
GROUP BY csUriStem
ORDER BY 2 DESC